from string import Template
from tack.lib import string_app, json_app, content_headers, status_codes
from util import build_row_dict_for_sql, build_empty_row_dict, fill_dict_from_form, build_columns_values_sql


class Sql:
    select_requested_by_commitment_id = """
    SELECT commitment_id
    FROM tmpo_commitment_detail
    WHERE commitment_id = $commitment_id
        AND state_key = 1
        AND state_finish IS NULL
    """        

    select_commitment_leg_columns = """
    SELECT * 
    FROM tmpo_commitment_leg
    WHERE 1 = 0
    """


    insert_commitment_leg = """
    INSERT INTO tmpo_commitment_leg
    """


    insert_driver = """
    INSERT INTO tmpo_commitment_leg_driver (commitment_leg_id, driver_id)
    VALUES ($commitment_leg_id, $driver_id)
    """


    insert_vehicle = """
    INSERT INTO tmpo_commitment_leg_vehicle (commitment_leg_id, vehicle_id)
    VALUES ($commitment_leg_id, $vehicle_id)
    """


def insert_requested_leg(environ, start_response):
##    return json_app(output)
    commitment_id = environ['dispatcher.groups']['commitment_id']
##    sql = Template(Sql.insert_vehicle).substitute(locals())
    cursor = environ['connection'].cursor()
    sql = Sql.select_requested_by_commitment_id
    sql = Template(sql).substitute(locals())
    cursor.execute(sql)
    rows = cursor.fetchall()
    if rows:
        return insert_leg(environ, start_response)
    start_response(status_codes.not_found, content_headers.default_plain())
    return ['404 Not Found']


def insert_leg(environ, start_response):
    commitment_id = environ['dispatcher.groups']['commitment_id']
    form = environ['form']
    cursor = environ['connection'].cursor()
    cursor.execute(Sql.select_commitment_leg_columns)
    rows = cursor.fetchall()
    state_dict = build_empty_row_dict(cursor.description)
    state_dict = fill_dict_from_form(state_dict, cursor.description, form)
    del state_dict['commitment_leg_id']
    state_dict['commitment_id'] = commitment_id
    sql = Sql.insert_commitment_leg + build_columns_values_sql(state_dict)
    cursor.execute(sql)
    environ['connection'].commit()
    cursor.execute('SELECT CAST(@@IDENTITY AS INT)')
    commitment_leg_id = cursor.fetchall()[0][0]
    if form.has_key('drivers'):
        drivers = form['drivers']
        for driver_id in drivers:
            if driver_id != '0':
                sql = Template(Sql.insert_driver).substitute(locals())
                cursor.execute(sql)
                environ['connection'].commit()
    if form.has_key('vehicles'):
        vehicles = form['vehicles']
        for vehicle_id in form['vehicles']:
            if vehicle_id != '0':
                sql = Template(Sql.insert_vehicle).substitute(locals())
                cursor.execute(sql)
                environ['connection'].commit()
    output = 'Added Leg'
    return json_app(output)(environ, start_response)
